package clothing.database;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.sql.Connection;

public class SaleManager {
	


			dbase db;

			public SaleManager() {
				db = new dbase();
			}

			public void insert(int uid, int item_id, double purchase_p, String purchase_d) {
				
				try {
					
					Class.forName("com.mysql.jdbc.Driver").newInstance();
					Connection conn = DriverManager.getConnection(
							"jdbc:mysql://199.17.162.76/Clothing", "root", "");

					System.out.println("Connected");
					//Statement st = conn.createStatement();
					
//					
//					String query = "insert into employees values('','" + firstname
//							+ "','" + mi + "','" + lastname + "','" + phone + "','"
//							+ address + "','" + type + "','" + username + "','"
//							+ password + "')";
//					System.out.println(query);
					Statement s;
					s = conn.createStatement();
					String status = "";
					
					String onequery = "Select * from items where Item_ID = '" + item_id+"'";
                    ResultSet rs = s.executeQuery(onequery);
                    while(rs.next())
                    {
                    	 status = rs.getString("Status");
                    }
					if(status.equalsIgnoreCase("In Stock"))
					{
						PreparedStatement p = 
							conn.prepareStatement("insert into sales values(?,?,?,?)");
						p.setInt(1, uid);
						p.setInt(2, item_id);
						p.setDouble(3, purchase_p);
						p.setString(4, purchase_d);
						
						
						p.executeUpdate();
					}
					
					
				} catch (InstantiationException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				


			}

			
			public ArrayList<Sale> getList() {
				ArrayList<Sale> list = new ArrayList();

				this.db.open();
				try {
					Statement s = this.db.getConn().createStatement();
					String query = "SELECT * from sales";

					ResultSet rs = s.executeQuery(query);

					while (rs.next()) {

						int id = rs.getInt("uid");
						int item_id = rs.getInt("Item ID");
						double purchase_price = rs.getDouble("Purchase Price");
						String purchase_date = rs.getString("Purchase Date");
						
						Sale ss = new Sale(id, item_id, purchase_price, purchase_date);
						list.add(ss);
					}

				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				return list;
			}
			
		
			
			public void remove(String uid)
			{
				this.db.open();
				Statement s;
				try {
					s = this.db.conn.createStatement();

					String query ="Delete from employees where uid = '"+uid+"'";
					
					s.execute(query);
					
					System.out.println(uid + "Deleted Successfully");
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		

	


}
